package com.example.coursehub.repository;

import java.util.List;
import java.util.Optional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import com.example.coursehub.entity.StudentExercise;
import com.example.coursehub.entity.StudentExerciseId;

public interface StudentExerciseRepository
        extends JpaRepository<StudentExercise, StudentExerciseId>, JpaSpecificationExecutor<StudentExercise> {

    // 修正：通过 c.course.courseId 访问 Course 实体的 courseId
    @Query("SELECT se FROM StudentExercise se " +
           "JOIN se.exercise e " +
           "JOIN e.classes c " +
           "WHERE se.user.id = :userId " +
           "AND se.state = :state " +
           "AND c.course.courseId = :courseId") // 修正路径
    List<StudentExercise> findByUserIdAndStateAndCourseId(
        @Param("userId") String userId, 
        @Param("state") String state, 
        @Param("courseId") String courseId
    );

    // 修正：通过 c.course.courseId 访问 Course 实体的 courseId
    @Query("SELECT se FROM StudentExercise se " +
            "JOIN se.exercise e " +
            "JOIN e.classes c " +
            "WHERE se.user.id = :userId " +
            "AND se.state IN :states " +
            "AND c.course.courseId = :courseId") // 修正路径
    List<StudentExercise> findByUserIdAndStateInAndCourseId(
            @Param("userId") String userId,
            @Param("states") List<String> states,
            @Param("courseId") String courseId);

    List<StudentExercise> findByExercise_ExerciseId(String exerciseId);

    Optional<StudentExercise> findByUser_UserIdAndExercise_ExerciseId(String userId, String exerciseId);
    List<StudentExercise> findByExercise_ExerciseIdAndState(String exerciseId, String state);
    List<StudentExercise> findByExercise_Classes_ClassesIdAndState(String classesId, String state);

    void deleteByExercise_ExerciseId(String exerciseId);

    int countByExercise_ExerciseIdAndState(String exerciseId, String submitted);
}
